﻿//创建时间: 2011-11-16
//创建作者: 李 城
//功能说明: 模板数据处理，继承  IDAL       
//最后修改: 2011-11-16

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using TRPOP.CMS.Template;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Data.Common;
using TRPOP.Config;
using TRPOP.CMS.Model;
using System.Text.RegularExpressions;
using TRPOP.Common;
using TRPOP.CMS.Business;
using TRPOP.CMS.Plugin;
using System.IO;

namespace TRPOP.CMS.TemplateSQLServerDAL
{
    public class TemplateDataManage : IDAL
    {
        #region 自动分页单页内容长度 GetAtuoContentCut

        public int GetAtuoContentCut()
        {
            return SYSConfig.SystemVar_ContentPageLength;
        }

        #endregion

        #region 获得模板变量  GetTemplateVar

        public string GetTemplateVar(string strVarKey)
        {
            return SystemVarInfoHelper.GetVarValueForCMSVar(strVarKey.Trim());
        }

        #endregion

        #region 通过Http返回PSNInfo

        public TRPOP.CMS.Model.PSNInfo GetInfoByHttp(string strHttp)
        {
            return new PSNManage().GetInfoByHttp(strHttp);
        }

        #endregion

        #region 获取内容信息 GetContent

        public object GetContent(string strIndexids, bool blnCache, bool blnDatatTable, string strReturnkey)
        {
            object objreturn = null;

            if (strIndexids.IsEmpty())
                return new DataTable();

            string strCacheKeys = string.Format(CacheKeys.Template_ContentInfo_IndexID, strIndexids);
            if ((objreturn = BaseDAL.mICache.Read(strCacheKeys)) != null)
            {
                if (blnDatatTable && (objreturn is DataRow))
                {
                    DataRow oDataRow = objreturn as DataRow;
                    DataTable returntable = oDataRow.Table.Copy();
                    returntable.ImportRow(oDataRow);
                    return returntable;
                }
                else if (blnDatatTable == false && (objreturn is DataTable) && (objreturn as DataTable).Rows.Count > 0)
                    return (objreturn as DataTable).Rows[0];
                return objreturn;
            }
            string strIndexSQLwhere = " ";
            if (strIndexids.Contains(","))
            {
                foreach (string strNodeid in strIndexids.Split(','))
                {
                    if (strNodeid.IsEmpty())
                        continue;
                    strIndexSQLwhere += strNodeid.ToInt() + ",";
                }
                if (strIndexSQLwhere.Length > 0)
                {
                    strIndexSQLwhere = string.Format(" where contentid  in ( {0} ) ", strIndexSQLwhere.ClearLastChar());
                }
                else
                    return null;
            }
            else
            {
                strIndexSQLwhere = string.Format(" where contentid  ={0} ", strIndexids);
            }


            string strExeSQL =
                               "DECLARE @tablename varchar(100)                             " + "\n" +
                               "select top 1 @tablename=ExtendTableName                     " +
                               " from {0}_content_Content                                   " +
                               "  {1}                                                       " + "\n" +
                               " if(len(@tablename)>0)                                      " + "\n" +
                               "EXECUTE(                                                    " + "\n" +
                               "  'select  {0}_content_Content.Contentid as IndexId , {0}_content_Content.* , '+@tablename+'.* '+'  {2}" +
                               "  from  {0}_content_Content , '+@tablename+' '              " +
                               "  +' {1}                                                    " + //mSqlWhereForContent +
                               "  and {0}_content_Content.ExtendId='+@tablename+'.'+@tablename+'id '" +
                               "       )                                                    ";
            strExeSQL = string.Format(strExeSQL,
                new object[] { SystemInfoHelper.getSystemInfo().DBprefixTableName, strIndexSQLwhere, (strReturnkey.Trim() == "" ? "" : ("," + strReturnkey)) + Helper.mContentColumns }
                                       );

            using (DataSet ds = BaseDAL.mICommonDAL.GetDataSetByReader(strExeSQL, SystemInfoHelper.getSystemInfo().CMSDataBaseConnection))
            {
                DataTable oReturnDt = null;

                #region 初始化处理
                if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    oReturnDt = ds.Tables[0];
                    string strMainField = Helper.PSNSourceURlFilterForContent(oReturnDt);
                    if (strMainField.IsEmpty() == false)
                    {
                        foreach (DataRow oTempRow in oReturnDt.Rows)
                        {
                            oTempRow[strMainField] = Helper.ConvertContentUrlByIndexID(oTempRow[strMainField].ToString(), strIndexids);
                        }
                    }
                }
                else
                    oReturnDt = new DataTable();
                #endregion

                if (blnDatatTable)
                    objreturn = oReturnDt;
                else
                {
                    if (oReturnDt.Rows.Count <= 0)
                        objreturn = oReturnDt.NewRow();
                    else
                        objreturn = oReturnDt.Rows.Count > 1 ? (object)oReturnDt : (object)oReturnDt.Rows[0];
                }
                BaseDAL.mICache.Save(strCacheKeys, objreturn, CacheKeys.TemplateTimeSecond);
                return objreturn;
            }
        }

        //内容页提取当前内容时使用，将已得到的数据源里的主内容字段图片进行转换，增加图片类型集合字段,这样可适用于多种类型的页面
        public DataTable GetContentWithImgResource(string strIndexids)
        {
            int intIndexId = 0;

            if (strIndexids.IsEmpty() || int.TryParse(strIndexids, out intIndexId) == false)
                return new DataTable();

            string strIndexSQLwhere = " where contentid  = " + strIndexids;

            string strExeSQL =
                               "DECLARE @tablename varchar(100)                             " + "\n" +
                               "select top 1 @tablename=ExtendTableName                     " +
                               " from {0}_content_Content                                   " +
                               "  {1}                                                       " + "\n" +
                               " if(len(@tablename)>0)                                      " + "\n" +
                               "EXECUTE(                                                    " + "\n" +
                               "  'select  {0}_content_Content.Contentid as IndexId , {0}_content_Content.* , '+@tablename+'.* '+'  {2}" +
                               "  from  {0}_content_Content , '+@tablename+' '              " +
                               "  +' {1}                                                    " + //mSqlWhereForContent +
                               "  and {0}_content_Content.ExtendId='+@tablename+'.'+@tablename+'id '" +
                               "       )                                                    ";
            strExeSQL = string.Format(strExeSQL, new object[] { SystemInfoHelper.getSystemInfo().DBprefixTableName, strIndexSQLwhere, Helper.mContentColumns });

            using (DataSet ds = BaseDAL.mICommonDAL.GetDataSetByReader(strExeSQL, SystemInfoHelper.getSystemInfo().CMSDataBaseConnection))
            {
                DataTable oReturnDt = null;

                if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    oReturnDt = ds.Tables[0];
                    string strMainField = Helper.PSNSourceURlFilterForContent(oReturnDt);
                    if (strMainField.IsEmpty() == false)
                    {
                        Helper.ConvertContentUrl(oReturnDt.Rows[0], strMainField);
                    }
                }
                else
                    oReturnDt = new DataTable();

                return oReturnDt;
            }
        }
        #endregion

        #region 获取附加发布 GetExtraPublish

        // 根据nodeid获取附加发布列表
        public DataTable GetExtraPublish(string strNodeids, bool blnCache, string strReturnkey)
        {
            if (strNodeids.IsEmpty())
                return new DataTable();

            string strNodeSQLwhere = " ";
            string strfields = " ";

            //结点
            strNodeSQLwhere = " WHERE 1=1  " + Helper.SqlWhereForNodeByIdAndTableName(strNodeids, false, string.Empty);

            //返回值
            strfields =
                (strReturnkey.IsEmpty() ? strfields : (strReturnkey + ","));

            string strExeSQL = " SELECT      {0}                          " +
                               "         [NodeAddionalPubId] as PublishID " +
                               "        ,[PubName] as PublishName         " +
                               "        ,[NodeId] as NodeID               " +
                               "        ,[Template] as PublishFileName    " +
                               "        ,[Url] as URL                     " +
                               "        ,[Intro] as Intro                 " +
                               " FROM {1}_node_NodeAddionalPub            " +
                               " {2}                                       ";
            strExeSQL = string.Format(strExeSQL,
                                       new object[] { strfields, SystemInfoHelper.getSystemInfo().DBprefixTableName, strNodeSQLwhere }
                                       );

            using (DataSet ds = BaseDAL.mICommonDAL.GetDataSetByReader(strExeSQL, SystemInfoHelper.getSystemInfo().CMSDataBaseConnection))
            {
                return ds.Tables[0];
            }

        }

        #endregion

        #region 获得结点

        //获取给定的结点的所有的子节点集合GetNodeListBySub或GetNodeListBySon、
        //获取给定的结点的结点集合GetNodeListBySet、
        //根据给定的结点获取所有的父节点集合GetNodeListByParent 、
        //根据给定的结点获取所有的同级结点集合GetNodeListByBrother

        //获取给定的结点的所有的子节点集合 
        public DataTable GetNodeListBySub(bool blnNodeguid, string strNodeguid, string strOrderby, string strIgnore, bool blnCache, string strReturnkey)
        {
            //结点条件
            string strNodeSQLTable = string.Format("  SELECT '-1,'+ArrChildId+','  AS _ArrChildId FROM [{0}_node_Node] WHERE 1=1 {1} ", SystemInfoHelper.getSystemInfo().DBprefixTableName, Helper.GetWhereSqlForNodeList(blnNodeguid, strNodeguid));

            //拼接SQL
            string strExecute = "SELECT {0}  [KeyWord] as  NodeGUID  ,NodeID ,[ModelId] AS TableID ,[Name] AS NodeName  ,[Name] AS Name ,[Url] AS  NodeURL  ,[Url]  ,[ParentId] AS parentNodeID " + "\n" +
                                "FROM   {1}_node_Node   , (  " + strNodeSQLTable + " ) TableWhere   " + "\n" +
                                "WHERE CHARINDEX(',' + cast(nodeid as nvarchar) + ',',_ArrChildId)>0 " + Helper.mSqlWhereForNode;

            if (strIgnore.IsEmpty() == false)
            {
                strExecute += " AND Nodeid not in ( " + strIgnore + ")";
            }

            strExecute = string.Format(strExecute,
                new object[] { strReturnkey.IsEmpty() ? string.Empty : (strReturnkey + ","), SystemInfoHelper.getSystemInfo().DBprefixTableName }
                                       );

            if (strOrderby.IsEmpty() == false)
            {
                strExecute += " ORDER BY " + strOrderby;
            }
            else
            {
                strExecute += " ORDER BY NodeId asc ";
            }


            //过滤兼容cmsware的旧的语法
            strExecute = Helper.SqlWhereReplace(strExecute);

            //获取数据
            using (DataSet oDataSet = BaseDAL.mICommonDAL.GetDataSetByReader(strExecute, SystemInfoHelper.getSystemInfo().CMSDataBaseConnection))
            {
                return oDataSet.Tables[0];
            }
        }

        //获取给定的结点的结点集合 
        public DataTable GetNodeListBySet(bool blnNodeguid, string strNodeguid, string strOrderby, string strIgnore, bool blnCache, string strReturnkey)
        {
            //结点条件
            string strNodeSQLwhere = Helper.GetWhereSqlForNodeList(blnNodeguid, strNodeguid);

            //拼接SQL语句
            string strExecute = "SELECT {0}  [KeyWord] as  NodeGUID  ,NodeID ,[ModelId] AS TableID ,[Name] AS NodeName  ,[Name] AS Name ,[Url] AS  NodeURL  ,[Url]  ,[ParentId] AS parentNodeID " + "\n" +
                                "FROM   {1}_node_Node  " + "\n" +
                                "WHERE  1=1  " + Helper.mSqlWhereForNode + strNodeSQLwhere;

            if (strIgnore.IsEmpty() == false)
            {
                strExecute += " AND Nodeid in ( " + strIgnore + ")";
            }

            if (strOrderby.IsEmpty() == false)
            {
                strExecute += " ORDER BY " + strOrderby;
            }
            else
            {
                strExecute += " ORDER BY NodeId asc ";
            }

            strExecute = string.Format(strExecute,
                new object[] { strReturnkey.Trim() == "" ? ("") : (strReturnkey + ","), SystemInfoHelper.getSystemInfo().DBprefixTableName }
                                       );

            //过滤兼容cmsware的旧的语法
            strExecute = Helper.SqlWhereReplace(strExecute);

            //获取数据
            using (DataSet oDataSet = BaseDAL.mICommonDAL.GetDataSetByReader(strExecute, SystemInfoHelper.getSystemInfo().CMSDataBaseConnection))
            {
                return oDataSet.Tables[0];
            }

        }

        //根据给定的结点获取所有的父节点集合 
        public DataTable GetNodeListByParent(bool blnNodeguid, string strNodeguid, string strOrderby, string strIgnore, bool blnCache, string strReturnkey)
        {
            //结点条件
            string strNodeSQLTable = string.Format("  SELECT '-1,'+ParentPath+','  AS _ParentPath FROM [{0}_node_Node] WHERE 1=1 {1} ", SystemInfoHelper.getSystemInfo().DBprefixTableName, Helper.GetWhereSqlForNodeList(blnNodeguid, strNodeguid));


            //拼接SQL
            string strExecute = "SELECT {0}  [KeyWord] as  NodeGUID  ,NodeID ,[ModelId] AS TableID ,[Name] AS NodeName  ,[Name] AS Name ,[Url] AS  NodeURL  ,[Url]  ,[ParentId] AS parentNodeID " + "\n" +
                                "FROM   {1}_node_Node   , (  " + strNodeSQLTable + " ) TableWhere   " + "\n" +
                                "WHERE CHARINDEX(',' + cast(nodeid as nvarchar) + ',',_ParentPath)>0 " + Helper.mSqlWhereForNode;

            if (strIgnore.IsEmpty() == false)
            {
                strExecute += " AND Nodeid in ( " + strIgnore + ")";
            }

            if (strOrderby.IsEmpty() == false)
            {
                strExecute += " ORDER BY " + strOrderby;
            }
            else
            {
                strExecute += " ORDER BY NodeId asc ";
            }

            strExecute = string.Format(strExecute,
                new object[] { strReturnkey.IsEmpty() ? string.Empty : (strReturnkey + ","), SystemInfoHelper.getSystemInfo().DBprefixTableName }
                                       );

            //过滤兼容cmsware的旧的语法
            strExecute = Helper.SqlWhereReplace(strExecute);

            //获取数据
            using (DataSet oDataSet = BaseDAL.mICommonDAL.GetDataSetByReader(strExecute, SystemInfoHelper.getSystemInfo().CMSDataBaseConnection))
            {
                return oDataSet.Tables[0];
            }
        }

        //根据给定的结点获取所有的同级结点集合 
        public DataTable GetNodeListByBrother(bool blnNodeguid, string strNodeguid, string strOrderby, string strIgnore, bool blnCache, string strReturnkey)
        {
            //结点条件
            string strNodeSQLwhere = string.Format(" AND [ParentId] IN ( SELECT   [ParentId]  FROM {0}_node_Node  WHERE 1=1 {1} ) ", SystemInfoHelper.getSystemInfo().DBprefixTableName, Helper.GetWhereSqlForNodeList(blnNodeguid, strNodeguid));

            //拼接SQL
            string strExecute = "SELECT {0}  [KeyWord] as  NodeGUID  ,NodeID ,[ModelId] AS TableID ,[Name] AS NodeName  ,[Name] AS Name ,[Url] AS  NodeURL  ,[Url]  ,[ParentId] AS parentNodeID " + "\n" +
                    "FROM   {1}_node_Node  " + "\n" +
                    "WHERE  1=1  " + Helper.mSqlWhereForNode + strNodeSQLwhere;

            if (strIgnore.IsEmpty() == false)
            {
                strExecute += " AND Nodeid not in ( " + strIgnore + ") ";
            }

            if (strOrderby.IsEmpty() == false)
            {
                strExecute += " ORDER BY " + strOrderby;
            }
            else
            {
                strExecute += " ORDER BY NodeId asc ";
            }

            strExecute = string.Format(strExecute,
                new object[] { strReturnkey.Trim() == "" ? ("") : (strReturnkey + ","), SystemInfoHelper.getSystemInfo().DBprefixTableName }
                                       );

            //过滤兼容cmsware的旧的语法
            strExecute = Helper.SqlWhereReplace(strExecute);

            //获取数据
            using (DataSet oDataSet = BaseDAL.mICommonDAL.GetDataSetByReader(strExecute, SystemInfoHelper.getSystemInfo().CMSDataBaseConnection))
            {
                return oDataSet.Tables[0];
            }
        }

        //等同 GetNodeListBySub
        public DataTable GetNodeListBySon(bool blnNodeguid, string strNodeguid, string strOrderby, string strIgnore, bool blnCache, string strReturnkey)
        {
            //结点条件
            string strNodeSQLTable = string.Format("  SELECT [NodeId] AS _ParentId FROM [{0}_node_Node] WHERE 1=1 {1} ", SystemInfoHelper.getSystemInfo().DBprefixTableName, Helper.GetWhereSqlForNodeList(blnNodeguid, strNodeguid));

            //拼接SQL
            string strExecute = "SELECT {0}  [KeyWord] as  NodeGUID  ,NodeID ,[ModelId] AS TableID ,[Name] AS NodeName  ,[Name] AS Name ,[Url] AS  NodeURL  ,[Url]  ,[ParentId] AS parentNodeID " + "\n" +
                                "FROM   {1}_node_Node   , (  " + strNodeSQLTable + " ) TableWhere   " + "\n" +
                                "WHERE ParentId =  _ParentId " + Helper.mSqlWhereForNode;

            if (strIgnore.IsEmpty() == false)
            {
                strExecute += " AND Nodeid not in ( " + strIgnore + ")";
            }

            strExecute = string.Format(strExecute,
                new object[] { strReturnkey.IsEmpty() ? string.Empty : (strReturnkey + ","), SystemInfoHelper.getSystemInfo().DBprefixTableName }
                                       );

            if (strOrderby.IsEmpty() == false)
            {
                strExecute += " ORDER BY " + strOrderby;
            }
            else
            {
                strExecute += " ORDER BY NodeId asc ";
            }

            //过滤兼容cmsware的旧的语法
            strExecute = Helper.SqlWhereReplace(strExecute);

            //获取数据
            using (DataSet oDataSet = BaseDAL.mICommonDAL.GetDataSetByReader(strExecute, SystemInfoHelper.getSystemInfo().CMSDataBaseConnection))
            {
                return oDataSet.Tables[0];
            }
        }

        #endregion

        #region 获取结点信息（包含 Navigation） GetNodeInfo

        public DataRow GetNodeInfo(bool blnNodeguid, string strNodeguid, bool blnCache, string strReturnkey)
        {
            //通过缓存去数据
            string strCacheKey = string.Format((blnNodeguid ? CacheKeys.Template_NodeInfo_GuidID : CacheKeys.Template_NodeInfo_ID)
                                                , strNodeguid);
            DataRow oDataRow = ((DataRow)BaseDAL.mICache.Read(strCacheKey));
            if (oDataRow != null)
                return oDataRow;

            //结点条件
            string strNodeSQLwhere = string.Empty;
            if (blnNodeguid)
            {
                strNodeSQLwhere = string.Format(" AND [KeyWord] ='{0}' ", strNodeguid.Replace("'", ""));
            }
            else
            {
                strNodeSQLwhere += strNodeguid.Replace("\"", "").ToInt();
                strNodeSQLwhere = string.Format(" AND nodeid ={0}", strNodeSQLwhere);
            }
            string strBaseColumn = " [KeyWord] as  NodeGUID,NodeID , [ModelId] as TableID ,[Name] as NodeName,[Name] as Name,[Url] as  NodeURL  ,[Url]   ,[ParentId] as parentNodeID  ,[ArrChildId] as ChildIds ,ParentPath as ParentIds , SEODescription as txtSEODescription , [SEOKey] as SEOKey , [SEOTitle] as SEOTitle ";
            string strExecute = "SELECT {0}   " + strBaseColumn + "\n" +
                                "FROM   {1}_node_Node                                   " + "\n" +
                                "WHERE  1=1                                         " + strNodeSQLwhere;
            strExecute = string.Format(strExecute, new object[] { strReturnkey.Trim() == "" ? ("") : (strReturnkey + ","), SystemInfoHelper.getSystemInfo().DBprefixTableName });

            //过滤兼容cmsware的旧的语法
            strExecute = Helper.SqlWhereReplace(strExecute);

            //获取数据
            using (DataSet oDataSet = BaseDAL.mICommonDAL.GetDataSetByReader(strExecute, SystemInfoHelper.getSystemInfo().CMSDataBaseConnection))
            {
                oDataRow = (oDataSet.Tables[0].Rows.Count > 0 ? oDataSet.Tables[0].Rows[0] : null);

                if (oDataRow is DataRow && oDataRow != null)
                    Helper.SaveInfoForNavigation(oDataRow);

                BaseDAL.mICache.Save(strCacheKey, oDataRow, CacheKeys.TemplateTimeSecond);
            }

            return oDataRow;
        }

        #endregion

        #region 获取评论信息列表 评论数  GetCommentList GetCommentCount

        public DataTable GetCommentList(string strIndexid, string strStart, string strNum, bool blnHiddenIp, bool blnCache, string strReturnkey, string strWhere)
        {
            //SQL
            string strExecute = @" SELECT * FROM
                                (
                                    SELECT  row_number() OVER ( ORDER BY  TempTable1.RecommendId DESC  ) AS IndexFlag  ,TempTable1.*
                                    FROM
                                    (
                                      SELECT  *
                                      FROM   [{0}_rec_Recom] , [{0}_rec_RecomContent]
                                      WHERE  [{0}_rec_Recom].RecommendId=[{0}_rec_RecomContent].RecomId 
                                             AND ContentId IN ({1})
                                             AND IsAudit='true'
                                    ) 
                                    TempTable1
                                ) TempTable2 
                                WHERE 1=1 {2}
                          ";
            strExecute = string.Format(strExecute,
                new object[] { SystemInfoHelper.getSystemInfo().DBprefixTableName, strIndexid, (strWhere.IsEmpty() ? "" : (" AND  " + strWhere)) }
                                       );

            //过滤兼容cmsware的旧的语法
            strExecute = Helper.SqlWhereReplace(strExecute);

            //获取数据
            using (DataSet oDataSet = BaseDAL.mICommonDAL.GetDataSetByReader(strExecute, SystemInfoHelper.getSystemInfo().CMSDataBaseConnection))
            {
                return oDataSet.Tables[0];
            }
        }

        public int GetCommentCount(string strIndexid)
        {
            string strExecute = @"  SELECT COUNT(0)
                                    FROM   [{0}_rec_Recom] , [{0}_rec_RecomContent]
                                    WHERE  [{0}_rec_Recom].RecommendId=[{0}_rec_RecomContent].RecomId 
                                           AND ContentId in ({1})
                                           AND IsAudit='true'
                                  ";

            strExecute = string.Format(strExecute,
                                       new object[] { SystemInfoHelper.getSystemInfo().DBprefixTableName, strIndexid }
                                       );

            //过滤兼容cmsware的旧的语法
            strExecute = Helper.SqlWhereReplace(strExecute);

            //获取数据
            using (DataSet oDataSet = BaseDAL.mICommonDAL.GetDataSetByReader(strExecute, SystemInfoHelper.getSystemInfo().CMSDataBaseConnection))
            {
                return oDataSet.Tables[0].Rows[0][0].ToString().ToInt();
            }
        }

        #endregion

        #region 统计方法 GetCount

        //用于对指定条件范围的内容进行计算统计等操作，如求计数COUNT、求总和SUM、求平均数AVG等这些功能。
        // 主要使用Function这个属性来定义需要的操作动作函数，其它属性同CMS_LIST的属性
        public string GetCount(string strFunction, string strNodeid, string strTableid, string strWhere, string strQuery, bool blnCache, string strReturnkey)
        {
            string strExeSQL = string.Empty;

            if (strQuery.IsEmpty() == false)
            {
                strExeSQL = strQuery;
            }
            else if (strFunction.IsEmpty())
            {
                return string.Empty;
            }
            else
            {

                string strNodeSQLwhere = Helper.SqlWhereForNodeByIdAndTableName(strNodeid, false, string.Empty);    // 结点范围  and nodeid in ...
                string strTableSQlwhere = string.Empty;   // 模型id范围 and ModelId in ...
                string strOtherTablename = string.Empty;

                // 模型范围
                if (strTableid.IsEmpty() == false && strTableid.Split(',').Length > 0)
                {
                    foreach (string strtempTableid in strTableid.Split(','))
                    {
                        strTableSQlwhere += strtempTableid.ToInt() + ",";
                    }
                    if (strTableSQlwhere.Length > 0)
                    {
                        strTableSQlwhere = strTableSQlwhere.ClearLastChar();
                        strTableSQlwhere = string.Format(" AND ModelId IN ({0}) ", strTableSQlwhere);
                    }
                }

                // 查找分表名称
                string strSearchTableSQL = string.Format(@" SELECT [TableName] 
                                                            FROM   [{0}_model_Model]  
                                                            WHERE  ModelId in ( SELECT TOP 1 [ModelId]  FROM {0}_node_Node where  1=1 {1} ) "
                                          , SystemInfoHelper.getSystemInfo().DBprefixTableName, strNodeSQLwhere + strTableSQlwhere);

                using (DataSet oDataSet = BaseDAL.mICommonDAL.GetDataSetByReader(strSearchTableSQL, SystemInfoHelper.getSystemInfo().CMSDataBaseConnection))
                {
                    if (oDataSet.Tables[0].Rows.Count > 0)
                        strOtherTablename = oDataSet.Tables[0].Rows[0][0].ToString();
                    else
                        return string.Empty;
                }

                //拼接SQL
                strExeSQL = string.Format("SELECT  {0} ", strFunction);
                strExeSQL += string.Format("FROM   {0}_content_Content ,{1} ", SystemInfoHelper.getSystemInfo().DBprefixTableName, strOtherTablename);
                strExeSQL += string.Format("WHERE  {0}_content_Content.ExtendId={1}.{1}id  {2}  {3}", SystemInfoHelper.getSystemInfo().DBprefixTableName, strOtherTablename, strNodeSQLwhere, Helper.mSqlWhereForContent);

            }

            //获取数据
            using (DataSet oDataSet = BaseDAL.mICommonDAL.GetDataSetByReader(strExeSQL, SystemInfoHelper.getSystemInfo().CMSDataBaseConnection))
            {
                return (oDataSet.Tables[0].Rows.Count > 0 ? oDataSet.Tables[0].Rows[0][0].ToString() : "");
            }

        }

        #endregion

        #region 直接根据 SQL 获取数据 GetDatabySQLQuery GetCountbySQLQuery

        //根据sql获取总数
        public DataTable GetDatabySQLQuery(string strQuery, string strNum, bool blnCache, string strReturnkey)
        {
            string strExeSQL = string.Empty;

            if (strNum.IsEmpty())
            {
                strExeSQL = strQuery;
            }
            else
            {
                if (strNum.Contains(","))
                {
                    if (strNum.Split(',').Length != 2)
                        return new DataTable();

                    int intStart = 0;
                    int intEnd = 0;
                    int.TryParse(strNum.Split(',')[0], out intStart);
                    int.TryParse(strNum.Split(',')[1], out intEnd);
                    if (intStart == 0 || intEnd == 0)
                        return null;

                    string strAddRowNumber = " ROW_NUMBER() OVER(ORDER BY newid() ASC) AS IndexFlag ";
                    if (strQuery.ToLower().Contains("order"))
                    {
                        string strOrderby = strQuery.ToLower().Substring(strQuery.ToLower().LastIndexOf("order"));
                        string strReg = @"order[\s]+?by[\s]+?[\w]+?([\s]+?[,][\w]+)*?[\s]+?((asc)|(desc))[\s]*?";
                        Regex oRegex = new Regex(strReg, RegexOptions.Multiline | RegexOptions.IgnoreCase);
                        Match oMatch = oRegex.Match(strOrderby);
                        if (oRegex.Match(strOrderby).Success)
                        {
                            strAddRowNumber = string.Format("ROW_NUMBER() OVER({0}) AS IndexFlag",
                                                             oMatch.Value
                                                             );
                        }
                    }

                    strExeSQL = string.Format(" SELECT  {0} , tabletemp.* FROM ( {1} ) tabletemp ",
                                              strAddRowNumber,
                                              strQuery
                                              );
                    Helper.SqlFormat(ref strExeSQL, string.Format(" AND  indexid  BETWEEN  {0} and {1}  ", intStart, intEnd));
                }
                else
                {
                    string strAddRowNumber = " ROW_NUMBER() OVER(ORDER BY newid() ASC) AS IndexFlag ";
                    if (strQuery.ToLower().Contains("order"))
                    {
                        string strOrderby = strQuery.ToLower().Substring(strQuery.ToLower().LastIndexOf("order"));
                        string strReg = @"order[\s]+?by[\s]+?[\w]+?([\s]+?[,][\w]+)*?[\s]+?((asc)|(desc))[\s]*?";
                        Regex oRegex = new Regex(strReg, RegexOptions.Multiline | RegexOptions.IgnoreCase);
                        Match oMatch = oRegex.Match(strOrderby);
                        if (oRegex.Match(strOrderby).Success)
                        {
                            strAddRowNumber = string.Format("ROW_NUMBER() OVER({0}) AS IndexFlag",
                                                             oMatch.Value
                                                            );
                        }
                    }

                    int intCount = 0;
                    int.TryParse(strNum, out intCount);
                    string strTop = string.Empty;
                    if (intCount > 0)
                    {
                        strTop = "  TOP " + intCount + "  ";
                    }

                    strExeSQL = string.Format(" SELECT  {2} {0} , tabletemp.* FROM ( {1} ) tabletemp ",
                              strAddRowNumber,
                              strQuery,
                              strTop
                              );
                }
            }

            //获取数据
            using (DataSet oDataSet = BaseDAL.mICommonDAL.GetDataSetByReader(strExeSQL, SystemInfoHelper.getSystemInfo().CMSDataBaseConnection))
            {
                return oDataSet.Tables[0];
            }

        }

        ////获取执行sql返回的信息总数
        public int GetCountbySQLQuery(string strQuery, bool blnCache, string strReturnkey)
        {
            using (DataSet oDataSet = BaseDAL.mICommonDAL.GetDataSetByReader(strQuery, SystemInfoHelper.getSystemInfo().CMSDataBaseConnection))
            {
                return oDataSet.Tables[0].Rows.Count;
            }
        }

        #endregion

        #region Search 获取信息列表

        //内部使用 根据nodeid 获取搜索列表
        private DataTable GetSearchListWithNodeId(string strNodeids, string strNum, string strField, string strKeywords, string strSeparator, string strIgnorecontentid, string strWhere, string strOrderby, bool blnExact, string strReturnkey, bool blnSubNodeid)
        {
            if (strField.IsEmpty() || strKeywords.IsEmpty() || strSeparator.IsEmpty())
                return new DataTable();

            //缓存
            string strCacheKey = string.Format(CacheKeys.Template_SearchList_info,
                                    strNodeids, strNum, strField, strKeywords, strSeparator, strIgnorecontentid, strWhere, strOrderby, blnExact, strReturnkey, blnSubNodeid);
            DataTable oDt = (DataTable)BaseDAL.mICache.Read(strCacheKey);

            if (oDt != null)
                return oDt;

            //获取数据
            //初始SQL语句
            string strBaseinfoSQL = Helper.GetListBaseSQL;
            if (strReturnkey.IsEmpty() == false)//如果指定返回值
            {
                strBaseinfoSQL = Helper.GetListBaseSQLWithNuColum.Replace("$Column$", strReturnkey);
            }

            // 如果Num为 5,6 类似于Mysql中 limit的写法
            if (strNum.Contains(',') && strNum.Split(',').Length == 2)
            {
                string TempPageWhere = " IndexFlag >=  " + strNum.Split(',')[0] + " AND  IndexFlag <= " + (int.Parse(strNum.Split(',')[1]) + int.Parse(strNum.Split(',')[0]) - 1).ToString();
                strWhere = (strWhere.IsEmpty() ? TempPageWhere : (strWhere + " and " + TempPageWhere));
            }

            bool blnpage = false;
            string strOrderByNodeid = string.Empty;
            string strPageOrderBy = string.Empty;
            string strPageWhere = string.Empty;

            if (strWhere.ToLower().Contains("indexflag"))
            {
                blnpage = true;
                Regex oRegex = new Regex(@"indexflag[\s]*[>][=]*[\s]*[\d]+[\s]*and[\s]*indexflag[\s]*[<][=]*[\s]*[\d]+", RegexOptions.IgnoreCase | RegexOptions.Multiline);
                Match oMatch = oRegex.Match(strWhere.ToLower());
                if (oMatch.Success)
                {
                    strWhere = strWhere.ToLower().Replace(oMatch.Value, " 1=1 ");
                    strPageWhere = " and " + oMatch.Value;
                }
            }

            // 结点条件
            string strNodeSQLwhere = string.Empty;//and nodeid in (1,2)
            if (strNodeids.Contains(","))
            {
                foreach (string strNodeid in strNodeids.Split(','))
                {
                    strNodeSQLwhere += strNodeid.ToInt() + ",";
                }
                if (strNodeSQLwhere.Length > 0)
                    strNodeSQLwhere = strNodeSQLwhere.ClearLastChar();
                strBaseinfoSQL += string.Format(" AND ContentTable.NodeId IN ({0}) ", strNodeSQLwhere);
            }
            else if (blnSubNodeid == false)
            {
                strBaseinfoSQL = strBaseinfoSQL.Replace("FROM", "  ,( case ContentTable.NodeId  when  " + strNodeids.ToInt() + " then -1 else 999999 end ) as NodeIDOrder   FROM ");
                strOrderByNodeid = " NodeIDOrder ASC , ";
            }
            else
            {
                List<NodeInfoWithLevel> list = new NodeManage().GetNodeListTree(strNodeids.ToInt(), 1, Helper.mSqlWhereForNode, true);
                foreach (NodeInfoWithLevel oinfo in list)
                {
                    strNodeSQLwhere += oinfo.NodeId.ToString() + ",";
                }
                if (strNodeSQLwhere.Length > 0)
                {
                    strNodeSQLwhere += strNodeids;
                }
                else
                {
                    strNodeSQLwhere = strNodeids;
                }
                strBaseinfoSQL += string.Format(" AND ContentTable.NodeId IN ({0}) ", strNodeSQLwhere);
            }

            // 查找分表
            string strOtherTablename = "";//tablename
            strOtherTablename = Helper.GetExtendTableName(strNodeids);
            strBaseinfoSQL = strBaseinfoSQL.Replace("$ExtTable$", strOtherTablename);

            // 数量
            string strSelectTop = "";//top 1
            int strTop = strNum.ToInt();
            strSelectTop = strTop == 0 ? " " : " top  " + strTop.ToString() + " ";
            strBaseinfoSQL = strBaseinfoSQL.Replace("$Top$", strSelectTop);

            // 条件
            strBaseinfoSQL += (strWhere.IsEmpty() ? (" ") : (" and  " + strWhere));
            //拼接Search的Where
            string strSearchWhere = Helper.GetSearchWhere(strField, strKeywords, strSeparator, blnExact);
            strBaseinfoSQL += (strSearchWhere.IsEmpty() ? (" ") : (" and  " + strSearchWhere));

            // 排除
            strBaseinfoSQL += "  " + Helper.SqlWhereingoreContentID(strIgnorecontentid);

            // 排序  __分页
            if (blnpage == false)
            {
                strBaseinfoSQL += strOrderby.IsEmpty() ? (" order by " + strOrderByNodeid + " ContentId desc ") : (" order by  " + strOrderby);
            }
            else
            {
                strPageOrderBy = strOrderby.IsEmpty() ? (" order by " + strOrderByNodeid + " ContentId desc ") : (" order by  " + strOrderby);
            }

            //组合SQL语句
            string strExecute = strBaseinfoSQL;
            if (blnpage)
            {
                strExecute = Helper.GetListPagerSQL;
                strExecute = strExecute.Replace("$SQLEXEC$", strBaseinfoSQL);
                strExecute = strExecute.Replace("$ORDERBY$", strPageOrderBy);
                strExecute = strExecute.Replace("$WHERE$", strPageWhere);
            }

            //过滤兼容cmsware的旧的语法
            strExecute = Helper.SqlWhereReplace(strExecute);

            using (DataSet ds = BaseDAL.mICommonDAL.GetDataSetByReader(strExecute, SystemInfoHelper.getSystemInfo().CMSDataBaseConnection))
            {
                oDt = ds.Tables[0];
                Helper.PSNSourceURlFilter(oDt);
                BaseDAL.mICache.Save(strCacheKey, oDt, CacheKeys.TemplateListTimeSecond);
                return oDt;
            }
        }

        //内部使用 获取搜索列表的总数量
        private int GetAllNumForSearchList(string strNodeids, string strField, string strKeywords, string strSeparator, string strIgnorecontentid, string strWhere, bool blnExact, bool blnSubNodeid)
        {
            if (strField.IsEmpty() || strKeywords.IsEmpty() || strSeparator.IsEmpty())
                return 0;

            string strCacheKey = string.Format(CacheKeys.Template_SearchList_Count,
                                        strNodeids, strField, strKeywords, strSeparator, strIgnorecontentid, strWhere, blnExact, blnSubNodeid);
            string strReturn = (string)BaseDAL.mICache.Read(strCacheKey);

            if (strReturn == null)
            {
                string strExecuteSQL = Helper.GetListTotalCount;

                //查找分表
                strExecuteSQL = strExecuteSQL.Replace("$ExtTable$", Helper.GetExtendTableName(strNodeids));

                // 结点
                strExecuteSQL += Helper.SqlWhereForNodeByIdAndTableName(strNodeids, blnSubNodeid, string.Empty);

                // 指定的条件
                strExecuteSQL += "  " +
                              (strWhere.IsEmpty() ? (" ") : (" and  " + strWhere));

                //拼接Search的Where
                string strSearchWhere = Helper.GetSearchWhere(strField, strKeywords, strSeparator, blnExact);
                strExecuteSQL += (strSearchWhere.IsEmpty() ? (" ") : (" and  " + strSearchWhere));

                // 排除
                string strIgnoreids = "";
                if (strIgnorecontentid.IsEmpty() == false)
                {
                    foreach (string strTemp in strIgnorecontentid.Split(','))
                    {
                        strIgnoreids += strTemp.ToInt() + ",";
                    }
                    if (strIgnoreids.Length > 0)
                    {
                        strIgnoreids = strIgnoreids.ClearLastChar();
                        strExecuteSQL += " " + " and ContentId not in ( " + strIgnoreids + " )";
                    }
                }

                //过滤兼容cmsware的旧的语法
                strExecuteSQL = Helper.SqlWhereReplace(strExecuteSQL);

                //获取数据
                using (DataSet oDataSet = BaseDAL.mICommonDAL.GetDataSetByReader(strExecuteSQL, SystemInfoHelper.getSystemInfo().CMSDataBaseConnection))
                {
                    strReturn = oDataSet.Tables[0].Rows[0][0].ToString();
                    int intReturn = strReturn.ToInt();

                    BaseDAL.mICache.Save(strCacheKey, intReturn, CacheKeys.TemplateListTimeSecond);

                    return intReturn;
                }
            }
            return strReturn.ToInt();
        }

        //根据结点id及其所有子节点、其他条件 获取 搜索列表
        public DataTable GetSearchWithAllSonByNodeid(string strNodeids, string strNum, string strField, string strKeywords, string strSeparator, string strIgnorecontentid, string strWhere, string strOrderby, bool blnExact, bool blnCache, string strReturnkey)
        {
            return GetSearchListWithNodeId(strNodeids, strNum, strField, strKeywords, strSeparator, strIgnorecontentid, strWhere, strOrderby, blnExact, strReturnkey, true);
        }

        //根据结点id及其所有子节点、其他条件 获取 搜索列表 数量
        public int GetSearchWithAllSonByNodeid(string strNodeids, string strField, string strKeywords, string strSeparator, string strIgnorecontentid, string strWhere, bool blnExact, bool blnCache)
        {
            return GetAllNumForSearchList(strNodeids, strField, strKeywords, strSeparator, strIgnorecontentid, strWhere, blnExact, true);
        }

        //根据结点id、其他条件 获取 搜索列表
        public DataTable GetSearchByNodeid(string strNodeids, string strNum, string strField, string strKeywords, string strSeparator, string strIgnorecontentid, string strWhere, string strOrderby, bool blnExact, bool blnCache, string strReturnkey)
        {
            return GetSearchListWithNodeId(strNodeids, strNum, strField, strKeywords, strSeparator, strIgnorecontentid, strWhere, strOrderby, blnExact, strReturnkey, false);
        }

        //根据结点id、其他条件 获取 搜索列表 数量
        public int GetSearchByNodeid(string strNodeids, string strField, string strKeywords, string strSeparator, string strIgnorecontentid, string strWhere, bool blnExact, bool blnCache)
        {
            return GetAllNumForSearchList(strNodeids, strField, strKeywords, strSeparator, strIgnorecontentid, strWhere, blnExact, false);
        }

        //根据Tableid、其他条件 获取 搜索列表
        public DataTable GetSearchByTableid(string strTableid, string strNum, string strField, string strKeywords, string strSeparator, string strIgnorecontentid, string strWhere, string strOrderby, bool blnExact, bool blnCache, string strReturnkey)
        {
            string strNodeSQLwhere = string.Empty;//and nodeid in (1,2)
            string strSelectTop = string.Empty;//top 1
            string strfieldWhere = string.Empty; //and ...
            string strIgnoreWhere = string.Empty;//and ...
            string strOtherTablename = string.Empty;//tablename
            string strOrderbySQL = string.Empty;//order by  id desc
            string strFields = " *  ";

            // 查找分表
            strOtherTablename = Helper.GetExtendTableNameByTableId(strTableid);

            // 数量
            int strTop = strNum.ToInt();
            if (strNum.Contains("page"))
                strTop = 0;
            strSelectTop = strTop == 0 ? " " : " TOP  " + strTop.ToString() + " ";

            //关键字
            strfieldWhere = Helper.GetSearchWhere(strField, strKeywords, strSeparator, blnExact);

            // 排除id
            strIgnoreWhere = Helper.SqlWhereingoreContentID(strIgnorecontentid);

            // 排序
            strOrderbySQL = strOrderby.IsEmpty() ? (" ORDER BY  ContentId DESC ") : (" ORDER BY  " + strOrderby);

            // 返回值
            strFields = strReturnkey.IsEmpty() ? strFields : strReturnkey;

            // SQL
            string strExecute = " " +
                                "      SELECT {0} row_number() OVER ( {2}  ) AS IndexFlag  ,{1} " + "\n" +
                                "      FROM                                                   " + "\n" +
                                "      (                                                      " + "\n" +
                                "        SELECT  {3}_content_Content.* {8} ,                  " + "\n" +
                                "                {4}.*                                        " + "\n" +
                                "        FROM {3}_content_Content , {4}                       " + "\n" +
                                "        WHERE {3}_content_Content.ExtendId={4}.{4}id {5} {6} " + "\n" +
                                "                {7}                                          " + "\n" + Helper.mSqlWhereForContent +
                                "        )  i                                                 ";
            strExecute = string.Format(strExecute,
                                       new object[] { strSelectTop, strFields, strOrderbySQL, SystemInfoHelper.getSystemInfo().DBprefixTableName, strOtherTablename.Trim(), 
                                                    strNodeSQLwhere,strfieldWhere,strIgnoreWhere,Helper.mContentColumns }
                                       );
            Helper.SqlFormat(ref strExecute, strWhere);

            //过滤兼容cmsware的旧的语法
            strExecute = Helper.SqlWhereReplace(strExecute);

            //获取数据
            using (DataSet oDataSet = BaseDAL.mICommonDAL.GetDataSetByReader(strExecute, SystemInfoHelper.getSystemInfo().CMSDataBaseConnection))
            {
                if (oDataSet.Tables[0].Rows.Count > 0)
                    return oDataSet.Tables[0];
                else
                    return null;
            }
        }

        public int GetSearchByTableid(string strTableid, string strField, string strKeywords, string strSeparator, string strIgnorecontentid, string strWhere, bool blnExact, bool blnCache)
        {
            string strNodeSQLwhere = string.Empty;//and nodeid in (1,2)
            string strfieldWhere = string.Empty; //and ...
            string strIgnoreWhere = string.Empty;//and ...
            string strOtherTablename = string.Empty;//tablename

            // 查找分表
            strOtherTablename = Helper.GetExtendTableNameByTableId(strTableid);

            //关键字
            strfieldWhere = Helper.GetSearchWhere(strField, strKeywords, strSeparator, blnExact);

            // 排除id
            strIgnoreWhere = Helper.SqlWhereingoreContentID(strIgnorecontentid);

            // SQL
            string strExecute = @"      SELECT  count(0)
                                        FROM {0}_content_Content , {1}                      
                                        WHERE {0}_content_Content.ExtendId={1}.{1}id {2} {3}  {4}   {5} {6}
                                 ";
            strExecute = string.Format(strExecute,
                                       new object[] { 
                                                    SystemInfoHelper.getSystemInfo().DBprefixTableName, 
                                                    strOtherTablename.Trim(), 
                                                    strNodeSQLwhere,
                                                    strfieldWhere,
                                                    strIgnoreWhere ,
                                                    Helper.mSqlWhereForContent,
                                                    strWhere.Replace("i.", "").Replace("c.", "")
                                                    }
                                       );



            //过滤兼容cmsware的旧的语法
            strExecute = Helper.SqlWhereReplace(strExecute);

            //获取数据
            using (DataSet oDataSet = BaseDAL.mICommonDAL.GetDataSetByReader(strExecute, SystemInfoHelper.getSystemInfo().CMSDataBaseConnection))
            {
                if (oDataSet.Tables[0].Rows.Count > 0)
                    return oDataSet.Tables[0].Rows[0][0].ToString().ToInt();
                else
                    return 0;
            }
        }

        #endregion

        #region List数据获取

        //基本的返回DataTable 的方法 
        // intType=1 时， 表示是 GetListByNodeids 
        // intType=2 时， 表示是 GetListByTableId
        // intType=3 时， 表示是 GetListByNodeGuid 暂不支持包含子节点的方法
        private DataTable GetListDataTable(int intType, string strNodeOrTableidOrGuids, string strNum, string strOrderby, string strWhere, string strIgnore, string strReturnkey, bool blnSubNodeid)
        {
            DataTable oDataTableReturn = new DataTable();

            string strBaseinfoSQL = Helper.GetListBaseSQL;

            //如果指定返回值
            if (strReturnkey.IsEmpty() == false)
            {
                strBaseinfoSQL = Helper.GetListBaseSQLWithNuColum.Replace("$Column$", strReturnkey);
            }

            // 如果Num为 5,10 取第5条到第10条数据
            if (strNum.Contains(',') && strNum.Split(',').Length == 2)
            {
                string TempPageWhere = " IndexFlag >=  " + strNum.Split(',')[0] + " AND  IndexFlag <= " + int.Parse(strNum.Split(',')[1]).ToString();
                strWhere = (strWhere.IsEmpty() ? TempPageWhere : (strWhere + " and " + TempPageWhere));
            }
            bool blnpage = false;
            string strPageOrderBy = "";
            string strPageWhere = "";
            if (strWhere.ToLower().Contains("indexflag"))
            {
                blnpage = true;
                Regex oRegex = new Regex(@"indexflag[\s]*[>][=]*[\s]*[\d]+[\s]*and[\s]*indexflag[\s]*[<][=]*[\s]*[\d]+", RegexOptions.IgnoreCase | RegexOptions.Multiline);
                Match oMatch = oRegex.Match(strWhere.ToLower());
                if (oMatch.Success)
                {
                    strWhere = strWhere.ToLower().Replace(oMatch.Value, " 1=1 ");
                    strPageWhere = " AND " + oMatch.Value;
                }
            }

            // 根据 intType 加条件 替换表名
            if (intType == 1)
            {
                strBaseinfoSQL += Helper.SqlWhereForNodeByIdAndTableName(strNodeOrTableidOrGuids, blnSubNodeid, "ContentTable");
                strBaseinfoSQL = strBaseinfoSQL.Replace("$ExtTable$", Helper.GetExtendTableName(strNodeOrTableidOrGuids));
            }
            else if (intType == 2)
            {
                strBaseinfoSQL = strBaseinfoSQL.Replace("$ExtTable$", Helper.GetExtendTableNameByTableId(strNodeOrTableidOrGuids));
            }
            else if (intType == 3)//GetListByNodeGuid
            {
                string strNodeSQLwhere = string.Empty;
                foreach (string strNodeKey in strNodeOrTableidOrGuids.Split(','))
                {
                    strNodeSQLwhere += "'" + strNodeKey.Replace("'", "") + "'" + ",";
                }
                if (strNodeSQLwhere.Length > 0)
                {
                    strNodeSQLwhere = strNodeSQLwhere.ClearLastChar();
                    strBaseinfoSQL += string.Format(" AND ContentTable.nodeid IN ( SELECT   nodeid  FROM [{0}_node_Node] WHERE [KeyWord] IN ( {1} ) ) ", SystemInfoHelper.getSystemInfo().DBprefixTableName, strNodeSQLwhere);
                    strBaseinfoSQL = strBaseinfoSQL.Replace("$ExtTable$", Helper.GetExtendTableNameByNodeSQL(string.Format(" AND  [KeyWord] IN ( {1} ) ", SystemInfoHelper.getSystemInfo().DBprefixTableName, strNodeSQLwhere)));
                }
            }

            // 数量
            string strSelectTop = string.Empty;//top 1
            int strTop = strNum.ToInt();
            strSelectTop = strTop == 0 ? " " : " TOP  " + strTop.ToString() + " ";
            strBaseinfoSQL = strBaseinfoSQL.Replace("$Top$", strSelectTop);

            // 条件
            strBaseinfoSQL += (strWhere.IsEmpty() ? (" ") : (" AND  " + strWhere));

            // 排除
            strBaseinfoSQL += "  " + Helper.SqlWhereingoreContentID(strIgnore);

            // 排序  __分页
            strOrderby = strOrderby.IsEmpty() ? (" ORDER BY  ContentId DESC ") : (" ORDER BY  " + strOrderby);
            if (blnpage == false)
                strBaseinfoSQL += strOrderby;
            else
            {
                strPageOrderBy = strOrderby;
            }

            //组合SQL语句
            string strExecute = strBaseinfoSQL;
            if (blnpage)
            {
                strExecute = Helper.GetListPagerSQL;
                strExecute = strExecute.Replace("$SQLEXEC$", strBaseinfoSQL);
                strExecute = strExecute.Replace("$ORDERBY$", strPageOrderBy);
                strExecute = strExecute.Replace("$WHERE$", strPageWhere);
            }

            //过滤兼容cmsware的旧的语法
            strExecute = Helper.SqlWhereReplace(strExecute);

            //获取数据
            using (DataSet ds = BaseDAL.mICommonDAL.GetDataSetByReader(strExecute, SystemInfoHelper.getSystemInfo().CMSDataBaseConnection))
            {
                oDataTableReturn = ds.Tables[0];
                Helper.PSNSourceURlFilter(oDataTableReturn);
                return oDataTableReturn;
            }
        }

        private int GetListCount(int intType, string strNodeOrTableidOrGuids, string strWhere, string strIgnore, bool blnSubNodeid)
        {
            string strExecuteSQL = Helper.GetListTotalCount;

            // 根据intType 加条件 替换表名
            if (intType == 1)
            {
                strExecuteSQL += Helper.SqlWhereForNodeByIdAndTableName(strNodeOrTableidOrGuids, blnSubNodeid, string.Empty);
                strExecuteSQL = strExecuteSQL.Replace("$ExtTable$", Helper.GetExtendTableName(strNodeOrTableidOrGuids));
            }
            else if (intType == 2)
            {
                strExecuteSQL = strExecuteSQL.Replace("$ExtTable$", Helper.GetExtendTableNameByTableId(strNodeOrTableidOrGuids));
            }
            else if (intType == 3)//GetListByNodeGuid
            {
                string strNodeSQLwhere = string.Empty;
                foreach (string strNodeKey in strNodeOrTableidOrGuids.Split(','))
                {
                    strNodeSQLwhere += "'" + strNodeKey.Replace("'", "") + "'" + ",";
                }
                if (strNodeSQLwhere.Length > 0)
                {
                    strNodeSQLwhere = strNodeSQLwhere.ClearLastChar();
                    strExecuteSQL += string.Format(" AND ContentTable.nodeid IN ( SELECT   nodeid  FROM [{0}_node_Node] WHERE [KeyWord] IN ( {1} ) ) ", SystemInfoHelper.getSystemInfo().DBprefixTableName, strNodeSQLwhere);
                    strExecuteSQL = strExecuteSQL.Replace("$ExtTable$", Helper.GetExtendTableNameByNodeSQL(string.Format(" AND  [KeyWord] IN ( {1} ) ", SystemInfoHelper.getSystemInfo().DBprefixTableName, strNodeSQLwhere)));
                }
            }

            // 指定的条件
            strExecuteSQL += "  " + (strWhere.IsEmpty() ? (" ") : (" AND  " + strWhere));

            // 排除
            strExecuteSQL += "  " + Helper.SqlWhereingoreContentID(strIgnore);

            //过滤兼容cmsware的旧的语法
            strExecuteSQL = Helper.SqlWhereReplace(strExecuteSQL);

            //获取数据
            using (DataSet oDataSet = BaseDAL.mICommonDAL.GetDataSetByReader(strExecuteSQL, SystemInfoHelper.getSystemInfo().CMSDataBaseConnection))
            {
                string strReturn = oDataSet.Tables[0].Rows[0][0].ToString();
                int intreturn = strReturn.ToInt();
                return intreturn;
            }
        }

        // 根据nodeid获得数据集
        public DataTable GetListByNodeids(string strNodeids, string strNum, string strOrderby, string strWhere, string strIgnore, bool blnCache, string strReturnkey, bool blnSubNodeid)
        {
            string strCacheKey = string.Format(CacheKeys.Template_List_info,
                                                strNodeids, strNum, strOrderby, strWhere, strIgnore, strReturnkey, blnSubNodeid);
            DataTable oDTreturn = (DataTable)BaseDAL.mICache.Read(strCacheKey);

            if (oDTreturn == null)
            {
                oDTreturn = GetListDataTable(1, strNodeids, strNum, strOrderby, strWhere, strIgnore, strReturnkey, blnSubNodeid);
                BaseDAL.mICache.Save(strCacheKey, oDTreturn, CacheKeys.TemplateListTimeSecond);
            }

            return oDTreturn;
        }

        //获取总数
        public int GetListByNodeids(string strNodeids, string strWhere, string strIgnore, bool blnCache, bool blnSubNodeid)
        {
            string strCacheKey = string.Format(CacheKeys.Template_List_Count, strNodeids, strWhere, strIgnore);
            string strReturn = null;

            strReturn = BaseDAL.mICache.Read(strCacheKey) as string;

            if (strReturn == null)
            {
                strReturn = GetListCount(1, strNodeids, strWhere, strIgnore, blnSubNodeid).ToString();
                BaseDAL.mICache.Save(strCacheKey, strReturn, CacheKeys.TemplateListTimeSecond);
            }

            return strReturn.ToInt();
        }

        //根据TableId获得数据集
        public DataTable GetListByTableId(string strTableId, string strNum, string strOrderby, string strWhere, string strIgnore, bool blnCache, string strReturnkey)
        {
            return GetListDataTable(2, strTableId, strNum, strOrderby, strWhere, strIgnore, strReturnkey, false);
        }
        //获取总数
        public int GetListByTableId(string strTableId, string strWhere, string strIgnore, bool blnCache)
        {
            return GetListCount(2, strTableId, strWhere, strIgnore, false);
        }

        //根据guid获得数据集
        public DataTable GetListByNodeGuid(string strNodeGuid, string strNum, string strOrderby, string strWhere, string strIgnore, bool blnCache, string strReturnkey)
        {
            return GetListDataTable(3, strNodeGuid, strNum, strOrderby, strWhere, strIgnore, strReturnkey, false);
        }
        //获取总数
        public int GetListByNodeGuid(string strNodeGuid, string strWhere, string strIgnore, bool blnCache)
        {
            return GetListCount(3, strNodeGuid, strWhere, strIgnore, false);
        }

        #endregion

        #region 搜索列表页获取搜索结果 GetPageSearchList

        // 搜索页 根据关键字搜索内容，只搜索 varchar nvarchar字段，并且该字段已设置为可搜索
        // 例如搜索 郑州 下雨  （以空格分开的，加入搜索条件的最多三(初始变量intSearchAndKey)个词，例如： 今天 郑州 下雨 fuck ， 则guck不会加入 ( ... and ...and ...and  )的行列 ）
        // 则sql条件是： ( [name] like '%郑州 下雨%' or ( [name] like '%郑州%' and  [name] like '%下雨%' ) ) or ( [keywords] like '%郑州 下雨%' or ( [keywords] like '%郑州%' and  [keywords] like '%下雨%' ) )
        public DataTable GetPageSearchList(int nodeid, int pageindex, int panenum, string key, string columns, out int recordcount)
        {

            int intSearchAndKey = 3;//搜索条件里的可识别的最多的key
            string strSearchCondation = string.Empty;//总体的sql条件
            string strTableName = string.Empty; //表名

            //获取搜索关键字  在这里将sql条件的模板组织好，在后面只需替换模板即可
            // {0} 字段名称
            string strSearchSqlTemplate = "  {0} LIKE '%" + key + "%'  ";
            string strSearchAndSql = string.Empty;
            MatchCollection oMatchs = new Regex(@"[\S]+", RegexOptions.IgnoreCase).Matches(key);
            int intSearchCount = 0;
            foreach (Match oMatch in oMatchs)
            {
                if (oMatch.Value.IsEmpty() == false && key != oMatch.Value)
                {
                    if (intSearchCount > intSearchAndKey)
                        break;
                    intSearchCount++;

                    strSearchAndSql += "     OR {0} LIKE '%" + oMatch.Value.Trim() + "%'  ";
                }
            }
            strSearchSqlTemplate = " ( " + strSearchSqlTemplate + strSearchAndSql + ")";

            //找到所有的字段
            ModelFieldManage modelfield = new ModelFieldManage();
            List<ModelFieldInfoWithTableName> olist = modelfield.GetWithTableNameListByNodeId(nodeid);
            if (olist == null)
            {
                recordcount = 0;
                return null;
            }

            //根据找到的字段组合SQL语句
            string strTitle = string.Empty; //搜索的title字段
            string strContent = string.Empty; //搜索的内容字段
            string strSearchKeyCondition = " 1 <> 1 ";//关键字的搜索条件
            string strShowColumns = "";
            foreach (ModelFieldInfoWithTableName FieldInfo in olist)
            {
                //表名
                if (strTableName.IsEmpty())
                {
                    strTableName = FieldInfo.TableName;
                }

                // 标题字段
                if (FieldInfo.IsTitleField)
                {
                    strTitle = "  " + strTableName + "." + FieldInfo.FieldName + " as Title";
                }

                //内容字段
                if (FieldInfo.IsMainField)
                {
                    strContent = "  " + strTableName + "." + FieldInfo.FieldName + " as Content";
                }

                //设置搜索条件
                if (FieldInfo.IsSearch && (FieldInfo.FieldTypeStr == "varchar" || FieldInfo.FieldTypeStr == "nvarchar"))
                {
                    strSearchKeyCondition += " OR " + string.Format(strSearchSqlTemplate, strTableName + "." + FieldInfo.FieldName);
                    strShowColumns += " , " + strTableName + "." + FieldInfo.FieldName;
                }

            }

            //组合总的SQL条件
            strSearchCondation = " AND (" + strSearchKeyCondition + ") AND b.PubStatus=1 ";


            #region 组合查询的SQL语句
            string strSQL = "  declare @columns nvarchar(100);\n\r"
                            + "     set @columns='';\n\r "
                            + "  declare @Condition nvarchar(3000);  \n\r"
                            + "     set @Condition=' " + strSearchCondation.Replace("'", "''") + "';           --查询条件 (即 where condition......) 不用加where关键字\n\r"
                            + "  declare @SortField nvarchar(100);		       --排序的字段名 (即 order by column asc/desc)\n\r"
                            + "     set @SortField=''; "
                            + "  declare @PageNumber int ; \n\r"
                            + "     set @PageNumber= " + pageindex + ";                        --当前页页码 (即Top currPage)\n\r"
                            + "  declare @PageSize int ; \n\r"
                            + "     set @PageSize=" + panenum + ";                        --分页大小\n\r"
                            + "  declare @RecordCount int ;					   --总记录数\n\r"
                            + "  -- 获得某一页的数据 --\n\r"
                            + "     set @Condition=' 1=1 '+ isnull(@Condition,'') \n\r"
                            + "  declare @SQLRecordCount nvarchar(4000)\n\r"
                            + "  declare @SQLTableView nvarchar(max)\n\r"
                            + "  declare @ResultSQL nvarchar(max)\n\r"
                            + "  declare @TableName nvarchar(500)--表\n\r"
                            + "  declare @ShowColumn nvarchar(1000)--列字段\n\r"
                            + "     set @TableName =  ' " + strTableName + "    left join " + SystemInfoHelper.getSystemInfo().DBprefixTableName + "_content_Content  b  on " + strTableName + "." + strTableName + "id=b.ExtendId  ' \n\r "
                            + "     set @ShowColumn = '  b.nodeid ,b.URL,b.PubTime as PublishDate  " + strShowColumns + " ' \n\r"
                            + "  if(@columns!='') begin \n\r"
                            + "    set @ShowColumn=@ShowColumn+','+@columns\n\r"
                            + "  end \n\r\n\r"

                            + "  declare @FirstRow	int  --记录开始位置\n\r"
                            + "  declare @LastRow	int	 --记录结束位置\n\r\n\r"

                            + "  set @FirstRow	= (@PageNumber - 1) * @PageSize + 1  \n\r"
                            + "  set @LastRow	= (@PageNumber - 1) * @PageSize + @PageSize \n\r"
                            + "  set @SQLRecordCount='\n\r"
                            + "  select @RecordCount=count(1) \n\r"
                            + "    from '+@TableName+' \n\r"
                            + "    where '+@Condition  \n\r"
                            + "  ----取得查询结果总数量-----\n\r"
                            + "  exec sp_executesql @SQLRecordCount,N'@RecordCount int out ',@RecordCount out\n\r"
                            + "  --select @RecordCount\n\r"
                             + " if(isnull(@SortField,'')='')\n\r"
                             + " begin\n\r"
                             + "	set @SQLTableView='\n\r"
                             + "	select ROW_NUMBER()OVER(ORDER BY b.AddTime desc) as RowNumber,'+@ShowColumn+'\n\r"
                             + "	from '+@TableName+' where '+@Condition\n\r"
                             + " end\n\r"

                             + " else begin \n\r"
                             + "	set @SQLTableView='\n\r"
                             + "	select ROW_NUMBER()OVER(ORDER BY '+@SortField+') as RowNumber, '+@ShowColumn+'\n\r"
                             + "	from '+@TableName+'  where '+@Condition\n\r"
                             + " end\n\r"
                            + "  set @ResultSQL='select * from ('+@SQLTableView+') TableView where RowNumber BETWEEN '+cast(@FirstRow as nvarchar(50))+' AND '+cast(@LastRow as nvarchar(50))+' ORDER BY RowNumber ASC'\n\r"
                            + "  execute (@ResultSQL)\n\r"
                            + "  select  (@RecordCount)\n\r"
                            + "  --print @SQLTableView\n\r"
                            + "";

            #endregion

            //获取数据
            recordcount = 0;
            using (DataSet oDataSet = BaseDAL.mICommonDAL.GetDataSet(strSQL, SystemInfoHelper.getSystemInfo().CMSDataBaseConnection))
            {
                if (oDataSet.Tables.Count > 0)
                {
                    recordcount = Convert.ToInt32(oDataSet.Tables[1].Rows[0][0]);
                }
                return oDataSet.Tables[0];
            }

        }


        public DataTable GetPageAdvancedSearchList(int nodeid, int pageindex, int panenum, string strCondation, string columns, out int recordcount)
        {

            string strSearchCondation = string.Empty;//总体的sql条件
            string strTableName = string.Empty; //表名

            //找到所有的字段
            ModelFieldManage modelfield = new ModelFieldManage();
            List<ModelFieldInfoWithTableName> olist = modelfield.GetWithTableNameListByNodeId(nodeid);
            if (olist == null)
            {
                recordcount = 0;
                return null;
            }

            //根据找到的字段组合SQL语句
            string strTitle = string.Empty; //搜索的title字段
            string strContent = string.Empty; //搜索的内容字段
            foreach (ModelFieldInfoWithTableName FieldInfo in olist)
            {
                //表名
                if (strTableName.IsEmpty())
                {
                    strTableName = FieldInfo.TableName;
                }

                // 标题字段
                if (FieldInfo.IsTitleField)
                {
                    strTitle = "  " + strTableName + "." + FieldInfo.FieldName + " as Title";
                }

                //内容字段
                if (FieldInfo.IsMainField)
                {
                    strContent = "  " + strTableName + "." + FieldInfo.FieldName + " as Content";
                }
            }

            //组合总的SQL条件
            strSearchCondation = " AND ( 1=1 " + strCondation + ") AND b.PubStatus=1 ";

            strSearchCondation = strSearchCondation.Replace("'", "''");

            #region 组合查询的SQL语句
            string strSQL = "  declare @columns nvarchar(100);\n\r"
                            + "     set @columns='" + columns.SQLFilter() + "';\n\r "
                            + "  declare @Condition nvarchar(3000);  \n\r"
                            + "     set @Condition=' " + strSearchCondation + "';           --查询条件 (即 where condition......) 不用加where关键字\n\r"
                            + "  declare @SortField nvarchar(100);		       --排序的字段名 (即 order by column asc/desc)\n\r"
                            + "     set @SortField=''; "
                            + "  declare @PageNumber int ; \n\r"
                            + "     set @PageNumber= " + pageindex + ";                        --当前页页码 (即Top currPage)\n\r"
                            + "  declare @PageSize int ; \n\r"
                            + "     set @PageSize=" + panenum + ";                        --分页大小\n\r"
                            + "  declare @RecordCount int ;					   --总记录数\n\r"
                            + "  -- 获得某一页的数据 --\n\r"
                            + "     set @Condition=' 1=1 '+ isnull(@Condition,'') \n\r"
                            + "  declare @SQLRecordCount nvarchar(4000)\n\r"
                            + "  declare @SQLTableView nvarchar(max)\n\r"
                            + "  declare @ResultSQL nvarchar(max)\n\r"
                            + "  declare @TableName nvarchar(500)--表\n\r"
                            + "  declare @ShowColumn nvarchar(1000)--列字段\n\r"
                            + "     set @TableName =  ' " + strTableName + "    left join " + SystemInfoHelper.getSystemInfo().DBprefixTableName + "_content_Content  b  on " + strTableName + "." + strTableName + "id=b.ExtendId  ' \n\r "
                            + "     set @ShowColumn = ' " + strTitle + " , " + strContent + " ,  b.nodeid ,b.URL,b.PubTime as PublishDate ' \n\r"
                            + "  if(@columns!='') begin \n\r"
                            + "    set @ShowColumn=@ShowColumn+','+@columns\n\r"
                            + "  end \n\r\n\r"

                            + "  declare @FirstRow	int  --记录开始位置\n\r"
                            + "  declare @LastRow	int	 --记录结束位置\n\r\n\r"

                            + "  set @FirstRow	= (@PageNumber - 1) * @PageSize + 1  \n\r"
                            + "  set @LastRow	= (@PageNumber - 1) * @PageSize + @PageSize \n\r"
                            + "  set @SQLRecordCount='\n\r"
                            + "  select @RecordCount=count(1) \n\r"
                            + "    from '+@TableName+' \n\r"
                            + "    where '+@Condition  \n\r"
                            + "  ----取得查询结果总数量-----\n\r"
                            + "  exec sp_executesql @SQLRecordCount,N'@RecordCount int out ',@RecordCount out\n\r"
                            + "  --select @RecordCount\n\r"
                             + " if(isnull(@SortField,'')='')\n\r"
                             + " begin\n\r"
                             + "	set @SQLTableView='\n\r"
                             + "	select ROW_NUMBER()OVER(ORDER BY b.AddTime desc) as RowNumber,'+@ShowColumn+'\n\r"
                             + "	from '+@TableName+' where '+@Condition\n\r"
                             + " end\n\r"

                             + " else begin \n\r"
                             + "	set @SQLTableView='\n\r"
                             + "	select ROW_NUMBER()OVER(ORDER BY '+@SortField+') as RowNumber, '+@ShowColumn+'\n\r"
                             + "	from '+@TableName+'  where '+@Condition\n\r"
                             + " end\n\r"
                            + "  set @ResultSQL='select * from ('+@SQLTableView+') TableView where RowNumber BETWEEN '+cast(@FirstRow as nvarchar(50))+' AND '+cast(@LastRow as nvarchar(50))+' ORDER BY RowNumber ASC'\n\r"
                            + "  execute (@ResultSQL)\n\r"
                            + "  select  (@RecordCount)\n\r"
                            + "  --print @SQLTableView\n\r"
                            + "";

            #endregion

            //获取数据
            recordcount = 0;
            using (DataSet oDataSet = BaseDAL.mICommonDAL.GetDataSet(strSQL, SystemInfoHelper.getSystemInfo().CMSDataBaseConnection))
            {
                if (oDataSet.Tables.Count > 0)
                {
                    recordcount = Convert.ToInt32(oDataSet.Tables[1].Rows[0][0]);
                }
                return oDataSet.Tables[0];
            }

        }

        #endregion

        #region 图片地址转换 ConvertToURLImagePath
        public void ConvertToURLImagePath(Dictionary<string, string> dicImage)
        {
            if (dicImage == null || dicImage.Count <= 0)
                return;

            ContentUpFileManage oContentUpFileManage = new ContentUpFileManage();
            ContentUpFileInfo oContentUpFileInfo = new ContentUpFileInfo();

            foreach (string oTempImg in dicImage.Keys)
            {
                //如果key本身就是http开头的路径，则不需要处理
                if (oTempImg.IsEmpty() || oTempImg.Trim().ToLower().StartsWith("http://"))
                {
                    dicImage[oTempImg] = oTempImg.Trim();
                    continue;
                }
                //否则 根据root，获取ContentUpFileInfo，获取 FileURL ，进行赋值
                oContentUpFileInfo = oContentUpFileManage.GetInfoByFileRoot(oTempImg.Trim());
                if (oContentUpFileInfo != null && oContentUpFileInfo.FileURL.IsEmpty() == false)
                {
                    dicImage[oTempImg] = oContentUpFileInfo.FileURL.Trim();
                }
                else
                {
                    dicImage[oTempImg] = oTempImg.Trim();
                }

            }
        }
        #endregion

        #region 图片压缩信息获取 autoMiniGetPhotoInfo
        //获取图片压缩处理信息，不压缩图片，仅仅返回相关的压缩信息
        public bool autoMiniGetPhotoInfo(string strphtotSrc, ref string strHttp, ref string strPhotoPath)
        {
            bool blnResult = false;
            strHttp = string.Empty;
            strPhotoPath = string.Empty;

            if (strphtotSrc.Trim().StartsWith(@"/") == false)
            {
                return blnResult;
            }

            //根据图片地址获取 ContentUpFileInfo
            ContentUpFileInfo oContentUpFileInfo = new ContentUpFileManage().GetInfoByFileRoot(strphtotSrc);

            //如果FileURL 以http:// 开头  并且 FileRoot以 / 开头
            if (oContentUpFileInfo != null && oContentUpFileInfo.FileURL.Trim().ToLower().StartsWith(@"http://") && oContentUpFileInfo.FileRoot.ToLower().StartsWith(@"/"))
            {
                strHttp = oContentUpFileInfo.FileURL.Trim().Substring(0, oContentUpFileInfo.FileURL.Trim().LastIndexOf(@"/") + 1);
                strPhotoPath = oContentUpFileInfo.FileRoot.Trim().Substring(0, oContentUpFileInfo.FileRoot.Trim().LastIndexOf(@"/") + 1);
                string strPhysicalPath = Common.Util.GetMapPath(strPhotoPath);
                if (!System.IO.Directory.Exists(strPhysicalPath))
                {
                    Directory.CreateDirectory(strPhysicalPath.Substring(0, strPhysicalPath.LastIndexOf("\\")));
                }
                blnResult = true;
            }

            return blnResult;
        }
        #endregion

    }
}
